﻿CREATE proc [dbo].[getFitestUserToRepair]
@workorder nvarchar(50)
as
declare @articleid int
select @articleid=articleid from Repairs where WordOrderId=@workorder
SELECT top 1 dbo.[User].Id
  FROM repair_prior_para
       INNER JOIN [User] ON dbo.[User].Id = dbo.repair_prior_para.userid
       INNER JOIN Article
          ON dbo.repair_prior_para.articleid = dbo.Article.ArticleId
       LEFT JOIN (SELECT count (*) AS repCount, RepEmp
                    FROM Repairs
                   WHERE StatusId = 2
                  GROUP BY RepEmp) tb1
          ON tb1.repEmp = dbo.[User].Id
 WHERE dbo.Article.ArticleId =@articleid
ORDER BY dbo.repair_prior_para.priorscore DESC,
         tb1.repCount,
         dbo.[User].firstlogtime DESC